#!/usr/bin/perl

#   Copyright 2007 Daniel J Shahin
#   This program is free software; you can redistribute it and/or modify
#   it under the terms of the GNU General Public License as published by
#   the Free Software Foundation; version 2 of the License.
# 
#   This program is distributed in the hope that it will be useful,
#   but WITHOUT ANY WARRANTY; without even the implied warranty of
#   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#   GNU General Public License for more details.

use lib qw( /home/dan/usr/local/lib/perl5/site_perl/5.8.7/
 /home/dan/usr/local/lib/perl5/site_perl/5.8.5/
		/home/dan/cpro.hijinxcomics.com/hijinx);

use XML::Simple;
use Data::Dumper;
use Business::ISBN qw(is_valid_checksum);
use DBI;
my $file = shift || die "usage: import_hijinx.pl <sqlite.db>";
my $sqlite = DBI->connect( "dbi:SQLite:dbname=$file", "", "" ) or die "can't connect!" ;
my $mysql = DBI->connect( "dbi:mysql:HIJINX01",  "dan", "passwordhere" ) or die "can't connect!" ;
#SQL statement handlers
my $sth1 = $mysql->prepare("select id, name, email, phone, checkin, frozen, zip, address from customers");
my $sth2 = $sqlite->prepare("insert or ignore into customers values(NULL,?,?,?,?,?,?,?,?,?,?,NULL,?)");
my $sth22 = $sqlite->prepare("insert or ignore into customers values(NULL,?,?,?,?,?,?,?,?,?,NULL,?,?)");
my $sth23 = $sqlite->prepare("update   customers set  credit = ?, bc_id = ? where email = ?");
my $sth3 = $mysql->prepare("select * from book_club");
my $sth4 = $sqlite->prepare("select count(id) from customers where email=?");
my $sth5 = $mysql->prepare("select * from books ");
my $sth6 = $sqlite->prepare("insert or replace into products values(NULL,?,?,?,?,?,?,3,NULL,NULL,NULL, NULL, ?,?,NULL)");
$sth1->execute();
while(my $cust  = $sth1->fetchrow_hashref){
	my ($fname,$lname) = split / /, $cust->{'name'};
	$fname = lc($fname);
	$lname = lc($lname);
	$sth2->execute($fname, $lname, $cust->{'email'}, $cust->{'address'}, '', $cust->{'zip'},
	$cust->{'phone'}, '', $cust->{'frozen'}, $cust->{'id'},"0");
}

$sth3->execute();

$sqlite->do("begin");
while(my $cust  = $sth3->fetchrow_hashref){
	my @names = split / /, $cust->{'name'};
	my $fname = shift @names;
	my $lname = join " ", @names ;
	$fname = lc($fname);
	$lname = lc($lname);
	#print "first:$fname last:$lname\n";
	#print "email " . $cust->{'email'};
	unless($cust->{'email'} eq ""){
		$sth4->execute($cust->{'email'});
		my ($count) = $sth4->fetchrow_array;
		if ($count > 0 ){
			#print "dupe email: " .  $cust->{'email'}  . "\n";
   			$sth23->execute($cust->{'credit'},$cust->{'id'},$cust->{'email'});
	
		}else{
			$sth22->execute($fname, $lname, $cust->{'email'}, $cust->{'address'}, '', $cust->{'zip'}, $cust->{'phone'}, '', 0, $cust->{'id'}, $cust->{'credit'});
		}
	}else{
		$sth22->execute($fname, $lname, $cust->{'email'}, $cust->{'address'}, '', $cust->{'zip'}, $cust->{'phone'}, '', 0, $cust->{'id'}, $cust->{'credit'});
	}
}
$sqlite->do("commit");

$sth5->execute() || die "can't execute 5";

	$sqlite->do("begin");
while (my $b= $sth5->fetchrow_hashref()){
	$sth6->execute($b->{'title'},$b->{'price'},$b->{'price'},$b->{'price'},NULL,NULL,$b->{'dcd'},$b->{'isbn'}) || die "can't do it: " . $sth6->errstr ;
}
	$sqlite->do("commit");



